﻿using System;
using System.Text;
using System.Data.SqlClient;
using System.Collections.Generic; 
using System.Data;
using System.Linq;
using Comm.Model;
using Comm.Utility;

namespace Comm.DAL
{
	public partial class OrdersDAO
	{
        OrderBookDAO orderbook = new OrderBookDAO();
        #region 向数据库中添加一条记录 +int Insert(Orders model)
        /// <summary>
        /// 向数据库中添加一条记录
        /// </summary>
        /// <param name="model">要添加的实体</param>
        /// <returns>插入数据的ID</returns>
        public int Insert(Orders model)
        {
            #region SQL语句
            const string sql = @"
INSERT INTO [dbo].[Orders] (
	[OrderId]
	,[OrderDate]
	,[UserId]
	,[TotalPrice]
	,[PostAddress]
	,[state]
)
VALUES (
	@OrderId
	,@OrderDate
	,@UserId
	,@TotalPrice
	,@PostAddress
	,@state
);select @@IDENTITY";
            #endregion
            var res = SqlHelper.ExecuteScalar(sql,
					new SqlParameter("@OrderId", model.OrderId),					
					new SqlParameter("@OrderDate", model.OrderDate),					
					new SqlParameter("@UserId", model.UserId),					
					new SqlParameter("@TotalPrice", model.TotalPrice),					
					new SqlParameter("@PostAddress", model.PostAddress),					
					new SqlParameter("@state", model.state)					
                );
            return res == null ? 0 : Convert.ToInt32(res);
        }
        #endregion

        #region 删除一条记录 +int Delete(string orderid)
        public int Delete(string orderid)
        {
            const string sql = "DELETE FROM [dbo].[Orders] WHERE [OrderId] = @OrderId";
            return SqlHelper.ExecuteNonQuery(sql, new SqlParameter("@OrderId", orderid));
        }
        #endregion

        #region 根据主键ID更新一条记录 +int Update(Orders model)
        /// <summary>
        /// 根据主键ID更新一条记录
        /// </summary>
        /// <param name="model">更新后的实体</param>
        /// <returns>执行结果受影响行数</returns>
        public int Update(Orders model)
        {
            #region SQL语句
            const string sql = @"
UPDATE [dbo].[Orders]
SET 
	[OrderId] = @OrderId
	,[OrderDate] = @OrderDate
	,[UserId] = @UserId
	,[TotalPrice] = @TotalPrice
	,[PostAddress] = @PostAddress
	,[state] = @state
WHERE [OrderId] = @OrderId";
            #endregion
            return SqlHelper.ExecuteNonQuery(sql,
					new SqlParameter("@OrderId", model.OrderId),					
					new SqlParameter("@OrderDate", model.OrderDate),					
					new SqlParameter("@UserId", model.UserId),					
					new SqlParameter("@TotalPrice", model.TotalPrice),					
					new SqlParameter("@PostAddress", model.PostAddress),					
					new SqlParameter("@state", model.state)					
                );
        }


        /// <summary>
        /// 根据主键ID更新一条记录
        /// </summary>
        /// <param name="model">更新后的实体</param>
        /// <returns>执行结果受影响行数</returns>
        public int Update1(string orderId)
        {

            string sql = "update Orders set state=1 where OrderId=@orderId";
            return SqlHelper.ExecuteNonQuery(sql, new SqlParameter("@orderId", orderId));
        }
        #endregion

        #region 分页查询一个集合 +IEnumerable<Orders> QueryList(int index, int size, object wheres, string orderField, bool isDesc = true)
        /// <summary>
        /// 分页查询一个集合
        /// </summary>
        /// <param name="index">页码</param>
        /// <param name="size">页大小</param>
        /// <param name="wheres">条件匿名类</param>
        /// <param name="orderField">排序字段</param>
        /// <param name="isDesc">是否降序排序</param>
        /// <returns>实体集合</returns>
        public IEnumerable<Orders> QueryList(int index, int size, object wheres, string orderField, bool isDesc = true)
        {
            var parameters = new List<SqlParameter>();
            var whereBuilder = new System.Text.StringBuilder();
            if (wheres != null)
            {
                var props = wheres.GetType().GetProperties();
                foreach (var prop in props)
                {
                    if (prop.Name.Equals("__o", StringComparison.InvariantCultureIgnoreCase))
                    {
                        // 操作符
                        whereBuilder.AppendFormat(" {0} ", prop.GetValue(wheres, null).ToString());
                    }
                    else
                    {
                        var val = prop.GetValue(wheres, null).ToString();
                        whereBuilder.AppendFormat(" [{0}] = @{0} ", prop.Name);
                        parameters.Add(new SqlParameter("@" + prop.Name, val));
                    }
                }
            }
            var sql = SqlHelper.GenerateQuerySql("Orders", new[] { "OrderId", "OrderDate", "UserId", "TotalPrice", "PostAddress", "state" }, index, size, whereBuilder.ToString(), orderField, isDesc);
            using (var reader = SqlHelper.ExecuteReader(sql, parameters.ToArray()))
            {
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        yield return SqlHelper.MapEntity<Orders>(reader);
                    }
                }
            }
        }
        #endregion

        #region 查询单个模型实体 +Orders QuerySingle(object wheres)
        /// <summary>
        /// 查询单个模型实体
        /// </summary>
        /// <param name="wheres">条件匿名类</param>
        /// <returns>实体</returns>
        public Orders QuerySingle(object wheres)
        {
            var list = QueryList(1, 1, wheres, null);
            return list != null && list.Any() ? list.FirstOrDefault() : null;
        }
        #endregion

        #region 查询单个模型实体 +Orders QuerySingle(string orderid)
        /// <summary>
        /// 查询单个模型实体
        /// </summary>
        /// <param name="orderid">key</param>
        /// <returns>实体</returns>
        public Orders QuerySingle(string orderid)
        {
            const string sql = "SELECT TOP 1 [OrderId], [OrderDate], [UserId], [TotalPrice], [PostAddress], [state] FROM [dbo].[Orders] WHERE [OrderId] = @OrderId";
            using (var reader = SqlHelper.ExecuteReader(sql, new SqlParameter("@OrderId", orderid)))
            {
                if (reader.HasRows)
                {
                    reader.Read();
                    return SqlHelper.MapEntity<Orders>(reader);
                }
                else
                {
                    return null;
                }
            }
        }
        #endregion

        #region 查询条数 +int QueryCount(object wheres)
        /// <summary>
        /// 查询条数
        /// </summary>
        /// <param name="wheres">查询条件</param>
        /// <returns>条数</returns>
        public int QueryCount(object wheres)
        {
            var parameters = new List<SqlParameter>();
            var whereBuilder = new System.Text.StringBuilder();
            if (wheres != null)
            {
                var props = wheres.GetType().GetProperties();
                foreach (var prop in props)
                {
                    if (prop.Name.Equals("__o", StringComparison.InvariantCultureIgnoreCase))
                    {
                        // 操作符
                        whereBuilder.AppendFormat(" {0} ", prop.GetValue(wheres, null).ToString());
                    }
                    else
                    {
                        var val = prop.GetValue(wheres, null).ToString();
                        whereBuilder.AppendFormat(" [{0}] = @{0} ", prop.Name);
                        parameters.Add(new SqlParameter("@" + prop.Name, val));
                    }
                }
            }
            var sql = SqlHelper.GenerateQuerySql("Orders", new[] { "COUNT(1)" }, whereBuilder.ToString(), string.Empty);
            var res = SqlHelper.ExecuteScalar(sql, parameters.ToArray());
            return res == null ? 0 : Convert.ToInt32(res);
        }
        #endregion
		
				/// <summary>
		/// 得到一个对象实体
		/// </summary>
		public Orders GetModel(int userId)
		{
			
			StringBuilder strSql=new StringBuilder();
            strSql.Append("select * from Orders  ");
            strSql.Append(" where UserId=@userId ");
						SqlParameter[] parameters = {
					new SqlParameter("@userId", SqlDbType.Int)};
			parameters[0].Value = userId;

			
			Orders model=new Orders();
			DataSet ds=SqlHelper.Query(strSql.ToString(),parameters);
			
			if(ds.Tables[0].Rows.Count>0)
			{
                Orders _orders = new Orders();
                for (var i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    if (ds.Tables[0].Rows[i]["OrderId"].ToString() != "")
                    {
                        string order = ds.Tables[0].Rows[i]["OrderId"].ToString();
                        _orders.OrdersList.Add(orderbook.GetModel(order));
                    }
                    if (ds.Tables[0].Rows[i]["OrderDate"].ToString() != "")
                    {
                        _orders.OrderDate = DateTime.Parse(ds.Tables[0].Rows[i]["OrderDate"].ToString());
                    }
                    if (ds.Tables[0].Rows[i]["UserId"].ToString() != "")
                    {
                        _orders.UserId = int.Parse(ds.Tables[0].Rows[i]["UserId"].ToString());
                    }
                    if (ds.Tables[0].Rows[i]["TotalPrice"].ToString() != "")
                    {
                        _orders.TotalPrice = decimal.Parse(ds.Tables[0].Rows[i]["TotalPrice"].ToString());
                    }
                    _orders.PostAddress = ds.Tables[0].Rows[i]["PostAddress"].ToString();
                    if (ds.Tables[0].Rows[i]["state"].ToString() != "")
                    {
                        _orders.state = int.Parse(ds.Tables[0].Rows[i]["state"].ToString());
                    }
                    model.OrdersList1.Add(_orders);
                }
                return model;
			}
			else
			{
				return null;
			}
		}


        public void GetModel1(Orders model, DataRow dr)
        {
            if (dr["OrderId"].ToString() != "")
                {
                  model.OrderId = dr["OrderId"].ToString();
                }
                if (dr["OrderDate"].ToString() != "")
                {
                    model.OrderDate = DateTime.Parse(dr["OrderDate"].ToString());
                }
                if (dr["UserId"].ToString() != "")
                {
                    model.UserId = int.Parse(dr["UserId"].ToString());
                }
                if (dr["TotalPrice"].ToString() != "")
                {
                    model.TotalPrice = decimal.Parse(dr["TotalPrice"].ToString());
                }
                model.PostAddress = dr["PostAddress"].ToString();
                if (dr["state"].ToString() != "")
                {
                    model.state = int.Parse(dr["state"].ToString());
                }
        }

		/// <summary>
		/// 获得数据列表
		/// </summary>
		public DataSet GetList(string strWhere)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select * ");
			strSql.Append(" FROM Orders ");
			if(strWhere.Trim()!="")
			{
				strSql.Append(" where "+strWhere);
			}
			return SqlHelper.Query(strSql.ToString());
		}
		
		/// <summary>
		/// 获得前几行数据
		/// </summary>
		public DataSet GetList(int Top,string strWhere,string filedOrder)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select ");
			if(Top>0)
			{
				strSql.Append(" top "+Top.ToString());
			}
			strSql.Append(" * ");
			strSql.Append(" FROM Orders ");
			if(strWhere.Trim()!="")
			{
				strSql.Append(" where "+strWhere);
			}
			strSql.Append(" order by " + filedOrder);
			return SqlHelper.Query(strSql.ToString());
		}


        ///执行存储过程完成下订单
        /// </summary>
        /// <param name="userId"></param>
        /// <param name="number"></param>
        /// <param name="address"></param>
        /// <returns></returns>
        public decimal CreateOrder(int userId, string number, string address)
        {
            SqlParameter[] pars = { 
                                  new SqlParameter("@orderId",SqlDbType.NVarChar,50),
                                  new SqlParameter("@userId",SqlDbType.Int,4),
                                  new SqlParameter("@address",SqlDbType.NVarChar,255),
                                  new SqlParameter("@totalMoney",SqlDbType.Money,8)
                                  };
            pars[0].Value = number;
            pars[1].Value = userId;
            pars[2].Value = address;
            pars[3].Direction = ParameterDirection.Output;
            SqlHelper.RunProcedure("create_order", pars);
            return Convert.ToDecimal(pars[3].Value);
        }

        public DataTable getUserOrder(int userId) 
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * from Orders O  ");
            strSql.Append("inner join OrderBook OB on Ob.OrderID=O.OrderId ");
            strSql.Append(" inner join Books B on Ob.BookID =B.Id ");
            strSql.Append(" where UserId=@userId ");
            SqlParameter[] parameters = {
					new SqlParameter("@userId", SqlDbType.Int)};
            parameters[0].Value = userId;
            DataSet dt = SqlHelper.Query(strSql.ToString(), parameters);
            return dt.Tables[0];
        }

        public DataTable getUserOrders(int userId) 
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * from Orders  ");
            strSql.Append(" where UserId=@userId ");
            SqlParameter[] parameters = {
					new SqlParameter("@userId", SqlDbType.Int)};
            parameters[0].Value = userId;
            DataSet dt = SqlHelper.Query(strSql.ToString(), parameters);
            return dt.Tables[0];
        }


        public IList<Orders> GetOrderList() 
        {
            string sql = "select * from Orders";
            DataTable dt = SqlHelper.ExecuteDataTable(sql);
            IList<Orders> list = null;
            if (dt.Rows.Count > 0) 
            {
               list= common.TBToList<Orders>.Table2List(dt);
            }
            return list;
        }

        public IList<Orders> GetOrderStatuIs0(int UserId)
        {
            string sql = "select * from Orders where state=1 and UserId=" + UserId + "";
            DataTable dt = SqlHelper.ExecuteDataTable(sql);
            IList<Orders> list = null;
            if (dt.Rows.Count > 0)
            {
                list = common.TBToList<Orders>.Table2List(dt);
            }
            return list;
        }
	}
}